﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using bdxyit.com.RecruitmentAppSys.IDAL;
using bdxyit.com.RecruitmentAppSys.Models;
using System.Data.SqlClient;
using bdxyit.com.RecruitmentAppSys.DBUtility;
using System.Data;

/***
*机器名：BDXYIT-HUI
*项目名称：$projectname$
*命名空间：bdxyit.com.RecruitmentAppSys.SQLDAL
*文件名：ProfessionalTypeListModels
*系统时间：3/25/2012 3:37:08 PM
*当前用户：huihui
*创建时间：2012
*
*修改时间：2012 3/25/2012 3:37:08 PM
*修改事件：
***/

namespace bdxyit.com.RecruitmentAppSys.SQLDAL
{
    public class ProfessionalTypeListDAL : IProfessionalTypeListDAL
    {
        private const string SQL_SELECT_PROFESSIONALTYPELIST = " select  ProTypeID,ProTypeName from ProfessionalTypeLists ";
        private const string SQL_DELETE_PROFESSIONALTYPELISTN_BY_PROTYPEID = " delete from ProfessionalTypeLists "
                                                                            + "  where ProTypeID=@ProTypeID";
        private const string SQL_UPDATE_PROFESSIONALTYPELIST_PROTYPEID = " update ProfessionalTypeLists set "
                                                                        + " ProTypeID=@ProTypeID,"
                                                                        + " ProTypeName=@ProTypeName"
                                                                        + "  where ProTypeID=@ProTypeID";
        private const string SQL_INSERT_PROFESSIONALTYPELIST = " insert into ProfessionalTypeLists("
                                                            + " ProTypeID,ProTypeName)"
                                                            + "  values ("
                                                            + " @ProTypeID,@ProTypeName)";
        private const string FIELD_ProTypeID = "@ProTypeID";
        private const string FIELD_ProTypeName = "@ProTypeName";

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <returns></returns>
        public List<ProfessionalTypeList> SelectProfessionalTypeList()
        {
            List<ProfessionalTypeList> ptl = new List<ProfessionalTypeList>();
            using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocal, CommandType.Text, SQL_SELECT_PROFESSIONALTYPELIST))
            {
                if (dr.Read())
                {
                    ProfessionalTypeList pt = new ProfessionalTypeList();
                    while (dr.Read())
                    {
                        pt.ProTypeID = dr["ProTypeID"].ToString();
                        pt.ProTypeName = dr["ProTypeName"].ToString();
                        ptl.Add(pt);
                    }

                }
            }
            return ptl;
        }

        /// <summary>
        /// 删除一条记录根据ProTypeID
        /// </summary>
        /// <param name="ProTypeID"></param>
        /// <returns></returns>
        public bool DeleteProfessionalTypeList(string ProTypeID)
        {
            bool flag = false;
            //创建参数
            SqlParameter parm = new SqlParameter(FIELD_ProTypeID, SqlDbType.VarChar, 10);
            parm.Value = ProTypeID;
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_DELETE_PROFESSIONALTYPELISTN_BY_PROTYPEID;
                cmd.Parameters.Add(parm);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="ptl"></param>
        /// <returns></returns>
        public bool UpdateProfessionalTypeList(ProfessionalTypeList ptl)
        {
            bool flag = false;
            SqlParameter[] ProfessionalTypeListParms = GetProfessionalTypeListParameters();
            SqlCommand cmd = new SqlCommand();


            ProfessionalTypeListParms[0].Value = ptl.ProTypeID;
            ProfessionalTypeListParms[1].Value = ptl.ProTypeName;


            foreach (SqlParameter parm in ProfessionalTypeListParms)
                cmd.Parameters.Add(parm);

            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_UPDATE_PROFESSIONALTYPELIST_PROTYPEID;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }

        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="cmodel"></param>
        /// <returns></returns>
        public bool InsertProfessionalTypeList(ProfessionalTypeList cmodel)
        {
            bool flag = false;
            SqlParameter[] ProfessionalTypeListParms = GetProfessionalTypeListParameters();
            SqlCommand cmd = new SqlCommand();
            //写添赋值语句


            ProfessionalTypeListParms[0].Value = cmodel.ProTypeID;
            ProfessionalTypeListParms[1].Value = cmodel.ProTypeName;

            foreach (SqlParameter parm in ProfessionalTypeListParms)
                cmd.Parameters.Add(parm);
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_INSERT_PROFESSIONALTYPELIST;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }


        private SqlParameter[] GetProfessionalTypeListParameters()
        {
            SqlParameter[] parms = new SqlParameter[] {
                    new SqlParameter(FIELD_ProTypeID, SqlDbType.VarChar,10),
					new SqlParameter(FIELD_ProTypeName, SqlDbType.VarChar,50)};
            return parms;
        }


    }
}